import java.io.IOException;
import java.sql.SQLException;
import java.sql.ResultSet;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * A Servlet to help view jpg images
 *
 * @author James Chy
 */

public class AddItems extends PhotoServlet {

    String subtitle;
    int i;
    String sessionid;
/*
    String initpath;
    String imgthmb;
*/
    static final long serialVersionUID = 0;
    
	public void imageProperties(String image_file)
	throws SQLException
	{

		String lvQuery;
		int lvCount;
		String lvImageUID="";
		ResultSet lvResultSet;
		
        // Get image_uid
        lvQuery = "SELECT images.uid FROM images " +
                "WHERE TRIM(images.image_file) = \"" + image_file + "\"";
        if(showQuery) out.println("Query: " + lvQuery + "<BR>");
		lvResultSet = executeQuery(lvQuery);
        while(lvResultSet.next()) lvImageUID = lvResultSet.getString("images.uid");

        out.println("<TABLE><TR><TD VALIGN=TOP>");

        // Begin Dates
        lvQuery = "SELECT COUNT(*) FROM image_date WHERE image_date.image_uid = " + lvImageUID;
        
        if(showQuery)
            out.println("Query:<BR>" + lvQuery + "<BR>");

		lvResultSet = executeQuery(lvQuery);

		lvResultSet.next();
        lvCount = lvResultSet.getInt("count(*)");

        lvQuery = "SELECT dates.uid, dates.date, image_date.image_uid " +
                   "FROM dates " +
                   "LEFT OUTER JOIN image_date " +
                   "ON dates.uid = image_date.date_uid " +
                   "AND image_date.image_uid = " + lvImageUID + " " +
                   "ORDER BY dates.date DESC";
		lvResultSet = executeQuery(lvQuery);
        
        if(showQuery)
            out.println("Query:<BR>" + lvQuery + "<BR>");
        out.println("<B>Dates</B><BR>");
        out.println("<TABLE>");

        if(lvCount==0)
            out.println("<TR><TD>None</TD></TR>");

        while (lvResultSet.next()) 
        {
            String lvDate = lvResultSet.getString("dates.date");   
            String lvOuter = lvResultSet.getString("image_date.image_uid"); 
            if(lvOuter!=null)
                out.println("<TR><TD>"+AddItems.MonthDayYear(lvDate)+"</TD></TR>");
        }
        out.println("</TABLE>");
        // End Dates

        out.println("</TD><TD VALIGN=TOP>");

        // Begin People
        lvQuery = "SELECT COUNT(*) FROM image_person WHERE image_person.image_uid = " + lvImageUID;
        
        if(showQuery)
            out.println("Query:<BR>" + lvQuery + "<BR>");

		lvResultSet = executeQuery(lvQuery);

		lvResultSet.next();
        lvCount = lvResultSet.getInt("count(*)");

        lvQuery = "SELECT people.*," +
                  " image_person.image_uid " +
                  "FROM people " +
                  "LEFT OUTER JOIN image_person " +
                  "ON people.uid = image_person.person_uid " +
                  "AND image_person.image_uid = " + lvImageUID + " " +
                  "ORDER BY people.lastName, people.firstName, people.middleName";
        
        if(showQuery)
            out.println("Query:<BR>" + lvQuery + "<BR>");

		lvResultSet = executeQuery(lvQuery);

        out.println("<B>People</B><BR>");
        out.println("<TABLE>");

        if(lvCount==0)
            out.println("<TR><TD>None</TD></TR>");

        while (lvResultSet.next()) 
        {
            String lvName=AddItems.FirstNameLastName(lvResultSet);
            String lvOuter = lvResultSet.getString("image_person.image_uid"); 
            if(lvOuter!=null)
                out.println("<TR><TD>" + lvName + "</TD></TR>");
        }
        out.println("</TR></TABLE>");
        // End People

        out.println("</TD><TD VALIGN=TOP>");

        // Begin Things
        lvQuery = "SELECT COUNT(*) FROM image_thing WHERE image_thing.image_uid = " + lvImageUID;
        
        if(showQuery)
            out.println("Query:<BR>" + lvQuery + "<BR>");

		lvResultSet = executeQuery(lvQuery);

		lvResultSet.next();
        lvCount = lvResultSet.getInt("count(*)");

        lvQuery = "SELECT things.uid, things.thing, " +
                  " image_thing.image_uid " +
                  "FROM things " +
                  "LEFT OUTER JOIN image_thing " +
                  "ON things.uid = image_thing.thing_uid " +
                  "AND image_thing.image_uid = " + lvImageUID + " " +
                  "ORDER BY things.thing";
        
        if(showQuery)
            out.println("Query:<BR>" + lvQuery + "<BR>");

		lvResultSet = executeQuery(lvQuery);

        out.println("<B>Things</B><BR>");
        out.println("<TABLE>");

        if(lvCount==0)
            out.println("<TR><TD>None</TD></TR>");

        while (lvResultSet.next()) 
        {
            String lvThing = lvResultSet.getString("things.thing");    
            String lvOuter = lvResultSet.getString("image_thing.image_uid"); 
            if(lvOuter!=null)
                out.println("<TR><TD>" + lvThing + "</TD></TR>");
        }
        out.println("</TABLE>");
        // End Things

        out.println("</TD><TD VALIGN=TOP>");

        // Begin Places
        lvQuery = "SELECT COUNT(*) FROM image_place WHERE image_place.image_uid = " + lvImageUID;
        
        if(showQuery)
            out.println("Query:<BR>" + lvQuery + "<BR>");

		lvResultSet = executeQuery(lvQuery);

		lvResultSet.next();
        lvCount = lvResultSet.getInt("count(*)");

        lvQuery = "SELECT places.uid, places.place, " +
                " image_place.image_uid " +
                "FROM places " +
                "LEFT OUTER JOIN image_place " +
                "ON places.uid = image_place.place_uid " +
                "AND image_place.image_uid = " + lvImageUID + " " +
                "ORDER BY places.place";
        
        if(showQuery)
            out.println("Query:<BR>" + lvQuery + "<BR>");

		lvResultSet = executeQuery(lvQuery);

        out.println("<B>Places</B><BR>");
        out.println("<TABLE>");

        if(lvCount==0)
            out.println("<TR><TD>None</TD></TR>");

        while (lvResultSet.next()) 
        {
            String lvPlace = lvResultSet.getString("places.place");    
            String lvOuter = lvResultSet.getString("image_place.image_uid"); 
            if(lvOuter!=null)
                 out.println("<TR><TD>" + lvPlace + "</TD></TR>");
        }
        out.println("</TABLE>");
        // End Places

        out.println("</TD></TR></TABLE>");
	}

    public int getCount(String inQuery)
    {
        ResultSet lvResultSet;
        
        
		lvResultSet=executeQuery(inQuery);
        int count=0;
        try
        {
			lvResultSet.next();
            count = lvResultSet.getInt("COUNT(*)");
        }
        catch(SQLException sqle)
        {
            out.println("SQL Exception Error "+sqle.toString() );
        }
    
        return count;
    }

    public void init(ServletConfig config) throws ServletException
    {
        super.init(config);
/*
        imgthmb = rb.getString("servletAlbum.imgthmb");
*/
    }

    public void doPost(HttpServletRequest request,
                        HttpServletResponse response)
        throws IOException, ServletException
    {
    	
        String dateValues[]=request.getParameterValues("dates");
        String peopleValues[]=request.getParameterValues("people");
        String thingValues[]=request.getParameterValues("things");
        String placeValues[]=request.getParameterValues("places");
        String smallquery="",
               startquery="", 
               imagequery="",
               datequery="", 
               personquery="", 
               thingquery="", 
               placequery="", 
               endquery="";
        int imagecount=0;
            

        response.setContentType("text/html");
        out = response.getWriter();

        sessionid=request.getParameter("sessionid");

        int page;
        if(request.getParameter("Page") != null)
            page=Integer.parseInt(request.getParameter("Page"));
        else 
            page=1;

        out.println("<HTML><HEAD><TITLE>Save Data</TITLE></HEAD><BODY>");
            
        smallquery = "SELECT images.uid, images.image_file " +
					 "FROM images, session_results " +
					 "WHERE session_results.session_uid = " + sessionid + " " +
					 "AND images.uid = session_results.image_uid";

        if (showQuery) out.println(smallquery+"<BR>");
        ResultSet rs = this.executeQuery(smallquery);
        int count=0;
        try
        {
            while(rs.next())
            {
            	String image=rs.getString("images.image_file");
            	if(showQuery) out.println(image+"<BR>");
            	String checkbox=request.getParameter(image);
            	if(showQuery) out.println(checkbox+"<BR>");
                if (checkbox != null && checkbox.equals("on"))
                {
                    imagecount++;
                    if(imagecount==1)
                        imagequery="AND ( ";
                    else
                        imagequery=imagequery+"OR ";

                    imagequery=imagequery+"<imageuid> = " + 
                  	           rs.getString("images.uid") + " ";
                } 
            }
            if(imagecount>0)
                imagequery=imagequery+") ";
        }
        catch(SQLException sqle)
        {
            out.println(sqle.getMessage());
        }
           
        if( (dateValues.length!=1) || (!dateValues[0].equals("0") ) )
        {
            for(i=0; i<dateValues.length; i++)
            {
                if(i==0) datequery = datequery + "WHERE ( ";
                else datequery = datequery + "OR ";
                datequery = datequery + "<tableuid> = " + dateValues[i] + " ";
                if( i==(dateValues.length-1) ) datequery = datequery + ") ";
            }
        }

        if( (peopleValues.length!=1) || (!peopleValues[0].equals("0") ) )
        {
            for(i=0; i<peopleValues.length; i++)
            {
                if(i==0) personquery = personquery + "WHERE ( ";
                else personquery = personquery + "OR ";

                personquery = personquery + "<tableuid> = " + peopleValues[i] + " ";

                if( i==(peopleValues.length-1) ) personquery = personquery + ") ";
            }
        }


        if( (thingValues.length!=1) || (!thingValues[0].equals("0") ) )
        {
            for(i=0; i<thingValues.length; i++)
            {
                if(i==0) thingquery = thingquery + "WHERE ( ";
                else thingquery = thingquery + "OR ";

                thingquery = thingquery + "<tableuid> = " + thingValues[i] + " ";

                if( i==(thingValues.length-1) ) thingquery = thingquery + ") ";
            }
        }

        if( (placeValues.length!=1) || (!placeValues[0].equals("0") ) )
        {
            for(i=0; i<placeValues.length; i++)
            {
                if(i==0) placequery = placequery + "WHERE ( ";
                else placequery = placequery + "OR ";

                placequery = placequery + "<tableuid> = " + placeValues[i] + " ";

                if( i==(placeValues.length-1) ) placequery = placequery + ") ";
            }
        }

        if(imagecount>0)
        {
            startquery = "DELETE FROM <tablename> ";
            endquery="";

            if( (dateValues.length!=1) || (!dateValues[0].equals("0") ) )
            {
                smallquery = startquery + imagequery + endquery;
                smallquery = smallquery.replaceAll("AND", "WHERE");
                smallquery = smallquery.replaceAll("<tablename>","image_date");
                smallquery = smallquery.replaceAll("<imageuid>", "image_date.image_uid");
                smallquery = smallquery.replaceAll("<tableuid>", "image_date.date_uid");
                if(showQuery) out.println("Query: " + smallquery + "<BR>");
                count = executeUpdate(smallquery);
            }
            if( (peopleValues.length!=1) || (!peopleValues[0].equals("0") ) )
            {
                smallquery = startquery + personquery + imagequery + endquery;
                smallquery = smallquery.replaceAll("<tablename>","image_person");
                smallquery = smallquery.replaceAll("<imageuid>", "image_person.image_uid");
                smallquery = smallquery.replaceAll("<tableuid>", "image_person.person_uid");
                if(showQuery) out.println("Query: " + smallquery + "<BR>");
                count = executeUpdate(smallquery);
            }
            if( (thingValues.length!=1) || (!thingValues[0].equals("0") ) )
            {
                smallquery = startquery + thingquery + imagequery + endquery;
                smallquery = smallquery.replaceAll("<tablename>","image_thing");
                smallquery = smallquery.replaceAll("<imageuid>", "image_thing.image_uid");
                smallquery = smallquery.replaceAll("<tableuid>", "image_thing.thing_uid");
                if(showQuery) out.println("Query: " + smallquery + "<BR>");
                count = executeUpdate(smallquery);
            }
            if( (placeValues.length!=1) || (!placeValues[0].equals("0") ) )
            {
                smallquery = startquery + placequery + imagequery + endquery;
                smallquery = smallquery.replaceAll("<tablename>","image_place");
                smallquery = smallquery.replaceAll("<imageuid>", "image_place.image_uid");
                smallquery = smallquery.replaceAll("<tableuid>", "image_place.place_uid");
                if(showQuery) out.println("Query: " + smallquery + "<BR>");
                count = executeUpdate(smallquery);
            }
            
            startquery = "INSERT INTO <tablename> (<field>, image_uid) " +
                         "SELECT <tableuid> AS <field>, <imageuid> AS image_uid " +
                         "FROM images, <maintable> ";
            endquery="";

            if( (dateValues.length!=1) || (!dateValues[0].equals("0") ) )
            {
                smallquery = startquery + datequery + imagequery + endquery;
                smallquery = smallquery.replaceAll("<tablename>","image_date");
                smallquery = smallquery.replaceAll("<field>","date_uid");
                smallquery = smallquery.replaceAll("<maintable>","dates");
                smallquery = smallquery.replaceAll("<tableuid>", "dates.uid");
                smallquery = smallquery.replaceAll("<imageuid>", "images.uid");
                if(showQuery) out.println("Query: " + smallquery + "<BR>");
                count = executeUpdate(smallquery);
            }
            if( (peopleValues.length!=1) || (!peopleValues[0].equals("0") ) )
            {
                smallquery = startquery + personquery + imagequery + endquery;
                smallquery = smallquery.replaceAll("<tablename>","image_person");
                smallquery = smallquery.replaceAll("<field>","person_uid");
                smallquery = smallquery.replaceAll("<maintable>","people");
                smallquery = smallquery.replaceAll("<tableuid>", "people.uid");
                smallquery = smallquery.replaceAll("<imageuid>", "images.uid");
                if(showQuery) out.println("Query: " + smallquery + "<BR>");
                count = executeUpdate(smallquery);
            }
            if( (thingValues.length!=1) || (!thingValues[0].equals("0") ) )
            {
                smallquery = startquery + thingquery + imagequery + endquery;
                smallquery = smallquery.replaceAll("<tablename>","image_thing");
                smallquery = smallquery.replaceAll("<field>","thing_uid");
                smallquery = smallquery.replaceAll("<maintable>","things");
                smallquery = smallquery.replaceAll("<tableuid>", "things.uid");
                smallquery = smallquery.replaceAll("<imageuid>", "images.uid");
                if(showQuery) out.println("Query: " + smallquery + "<BR>");
                count = executeUpdate(smallquery);
            }
            if( (placeValues.length!=1) || (!placeValues[0].equals("0") ) )
            {
                smallquery = startquery + placequery + imagequery + endquery;
                smallquery = smallquery.replaceAll("<tablename>","image_place");
                smallquery = smallquery.replaceAll("<field>","place_uid");
                smallquery = smallquery.replaceAll("<maintable>","places");
                smallquery = smallquery.replaceAll("<tableuid>", "places.uid");
                smallquery = smallquery.replaceAll("<imageuid>", "images.uid");
                if(showQuery) out.println("Query: " + smallquery + "<BR>");
                count = executeUpdate(smallquery);
            }
            
            out.println("<H2>Settings Saved, Updated " + String.valueOf(count) +"</H2><BR>");
        }
        else
        {
        	out.println("<H2>No Images Selected</H2>");
        }
        out.println("<FORM ACTION=AddItems METHOD=GET>");
        out.println("<INPUT TYPE=HIDDEN NAME=sessionid VALUE="+sessionid+">");
		out.println("<INPUT TYPE=HIDDEN NAME=Page VALUE="+String.valueOf(page)+">");
        out.println("<INPUT TYPE=SUBMIT VALUE=\"Return to Add Items\">");
        out.println("</FORM>");
        out.println("</BODY></HTML>");

    }

    public void doGet(HttpServletRequest request,
                      HttpServletResponse response)
        throws IOException, ServletException
    {
        response.setContentType("text/html");
        out = response.getWriter();
        String query;
        ResultSet rs;

        out.println("<HTML>");
        out.println("<BODY BGCOLOR=\"white\">");
        out.println("<HEAD>");

        subtitle = "Add Items";

        sessionid=request.getParameter("sessionid");

        if(showQuery) out.println("sessionid: " + sessionid);

        int page;
        if(request.getParameter("Page") != null)
            page=Integer.parseInt(request.getParameter("Page"));
        else 
            page=1;

		int rows=10;
		int columns=1;
        int start;
        int end;

        start=(page-1)*rows*columns + 1;
        end=page*rows*columns;

        out.println("<TITLE>" + title + " - " + subtitle + "</TITLE>");  
        out.println("</HEAD>");
        out.println("<BODY>");
        out.println("<FORM ACTION=SearchResults METHOD=POST>");
        out.println("<INPUT TYPE=HIDDEN NAME=sessionid VALUE="+sessionid+">");
        out.println("<INPUT TYPE=SUBMIT VALUE=\"Search Results\">");
        out.println("</FORM>");
        out.println("<FORM ACTION=AddItems METHOD=POST>");
        out.println("<TABLE>");
        out.println("<TR><TD COLSPAN=4>");
        out.println("<INPUT TYPE=HIDDEN NAME=sessionid VALUE="+sessionid+">");
        out.println("<INPUT TYPE=HIDDEN NAME=Page VALUE="+String.valueOf(page)+">");
        out.println("<INPUT TYPE=SUBMIT VALUE=\"Add Items\">");
        out.println("</TD></TR>");
        out.println("<TR>");

        // Dates
        out.println("<TD>");
        query = "SELECT uid, date " +
                "FROM dates " +
                "ORDER BY date DESC";
            
        if(showQuery)
            out.println("Query:<BR>" + query + "<BR>");

        rs = executeQuery(query);

        out.println("<B><A HREF=\"MaintainDates\" TARGET=\"_blank\">Dates</A></B><BR>");
        out.println("<SELECT NAME=\"dates\" SIZE=\"20\">");
        out.println("<OPTION SELECTED VALUE=\"0\">&lt;None&gt;");
        try
        {
            while (rs.next()) 
            {
                String uid = rs.getString("uid");
                String value = rs.getString("date");    
                out.print("<OPTION ");
                out.println("VALUE=\"" + uid + "\">" + AddItems.MonthDayYear(value));
            }
        }
        catch(SQLException sqle)
        {
        	out.println(sqle.getMessage());
        }
        out.println("</SELECT>");
        out.println("</TD>");

        // People
        out.println("<TD>");
        query = "SELECT uid, firstName, lastName, middleName, suffix " +
                "FROM people " +
                "ORDER BY lastName, firstName";
            
        if(showQuery)
            out.println("Query:<BR>" + query + "<BR>");

        rs = executeQuery(query);

        out.println("<B><A HREF=\"MaintainPeople\" TARGET=\"_blank\">People</A></B><BR>");
        out.println("<SELECT NAME=\"people\" SIZE=\"20\" MULTIPLE>");
        out.println("<OPTION SELECTED VALUE=\"0\">&lt;None&gt;");
        try
        {
            while (rs.next()) 
            {
                String uid = rs.getString("uid");
                String value = AddItems.FirstNameLastName(rs);
                out.print("<OPTION ");
               out.println("VALUE=\"" + uid + "\">" + value);
            }
        }
        catch(SQLException sqle)
        {
        	out.println(sqle.getMessage());
        }
        out.println("</SELECT>");
        out.println("</TD>");

        // Things
        out.println("<TD>");
        query = "SELECT uid, thing " +
                "FROM things " +
                "ORDER BY thing";
            
        if(showQuery)
            out.println("Query:<BR>" + query + "<BR>");

        rs = executeQuery(query);

        out.println("<B><A HREF=\"MaintainThings\" TARGET=\"_blank\">Things</A></B><BR>");
        out.println("<SELECT NAME=\"things\" SIZE=\"20\" MULTIPLE>");
        out.println("<OPTION SELECTED VALUE=\"0\">&lt;None&gt;");
        try
        {
            while (rs.next()) 
            {
                String uid = rs.getString("uid");
                String value = rs.getString("thing");
                out.print("<OPTION ");
                out.println("VALUE=\"" + uid + "\">" + value);
            }
        }
        catch(SQLException sqle)
        {
        	out.println(sqle.getMessage());
        }
    
        out.println("</SELECT>");
        out.println("</TD>");

        // Places
        out.println("<TD>");
        query = "SELECT uid, place " +
                "FROM places " +
                "ORDER BY place";
            
        if(showQuery)
            out.println("Query:<BR>" + query + "<BR>");

        rs = executeQuery(query);

        out.println("<B><A HREF=\"MaintainPlaces\" TARGET=\"_blank\">Places</A></B><BR>");
        out.println("<SELECT NAME=\"places\" SIZE=\"20\" MULTIPLE>");
        out.println("<OPTION SELECTED VALUE=\"0\">&lt;None&gt;");
        try
        {
            while (rs.next()) 
            {
                String uid = rs.getString("uid");
                String value = rs.getString("place");
                out.print("<OPTION ");
                out.println("VALUE=\"" + uid + "\">" + value);
            }
        }
        catch(SQLException sqle)
        {
        	out.println(sqle.getMessage());
        }
        out.println("</SELECT>");
        out.println("</TD>");
        out.println("</TR></TABLE>");

        String getSession = "SELECT session_uid, image_file FROM session_results WHERE session_uid = " + sessionid + " ORDER BY (date), (image_file);";
        if(showQuery)
            out.println("Get Session: "+getSession+"<BR>");

        rs = executeQuery(getSession);

        try
        {
            for(i=1;rs.next(); i++) {
            	if(showQuery) 
            	   out.println("Query found:" + rs.getString("image_file")); 
            }
            
            out.println((i-1)+" image(s) found.<BR>");
            out.print("Displaying "+start+" through ");
            if(end>(i-1))
                out.print(String.valueOf(i-1)+"<BR>");
            else
                out.print(String.valueOf(end));
			
            out.println("<TABLE BORDER=0><TR>");
            if(page>1)
            {
                out.println("<TD>");
            	out.println("<A HREF=AddItems?sessionid="+sessionid+"&Page="+String.valueOf(page-1)+" METHOD=GET>Prev</A>");
            	out.println("</TD>");
            }
            if(end<(i-1))
            {
                out.println("<TD>");
            	out.println("<A HREF=AddItems?sessionid="+sessionid+"&Page="+String.valueOf(page+1)+" METHOD=GET>Next</A>");
            	out.println("</TD>");
            }
			out.println("</TABLE>");

		}
		catch(SQLException sqle)
		{
			out.println("SQL Exception error "+sqle.toString() );
		}    

		rs = executeQuery(getSession);

		try
		{
            out.println("<TABLE>");
            for(i=1;rs.next(); i++) 
            {
				if(i>=start && i<=end)
				{
	                String image_file = rs.getString("image_file");
	                out.println("<TR>");
	                out.println("<TD>");
	                out.println("<A HREF=\"OneImage?image=" + image_file + "&sessionid=" + sessionid + "&Page=" + page + "\">");

					out.print("<IMAGE SRC=\"" + imgthmb + "/" + image_file +"\" ");
					out.print("VSPACE=\"0\" HSPACE=\"0\" BORDER=\"0\"");
					out.println(">");

	                out.println("</A>");
	                out.println("</TD>"); 
	                out.println("<TD>");
	                out.println("<INPUT TYPE=CHECKBOX CHECKED NAME=\""+image_file+"\">");
	                out.println("</TD>");
	                out.println("<TD>");
					imageProperties(image_file);
	                out.println("</TD>");
	                out.println("</TR>");
				}
				
            }
            out.println("</TABLE>");
        }
        catch(SQLException sqle)
        {
            out.println("SQL Exception error "+sqle.toString() );
        }    

        out.println("</FORM>");

        out.println("</BODY>");
        out.println("</HTML>");
    	
    }
}